import pandas as pd
import openpyxl
from openpyxl import load_workbook
from openpyxl.styles import PatternFill, Font
import shutil

def copy_file(source, destination):
    try:
        shutil.copy(source, destination)
        print(f"{destination} 文件复制成功！")
    except IOError as e:
        print("文件复制失败:", e)

def read_excel(file_path, sheet_name=0):
    """
    读取Excel文件中指定的sheet，默认读取第一个sheet，并返回数据
    """
    data = pd.read_excel(file_path, sheet_name=sheet_name)
    return data



def compare_excel(excel1, excel2, output_file, sheet_name, threshold):
    """
    比较两个Excel文件的数值差异，并将差值写入新的Excel文件中
    """
    excel2_rows, excel2_cols = excel2.shape

    # 打开Excel文件
    wb = load_workbook(output_file)
    # 选择工作表
    sheet = wb[sheet_name]

    # 遍历每个单元格，获取差值
    for row in range(1, excel2_rows):
        for col in range(3, excel2_cols):
            excel1_cell = float(excel1.iloc[row, col])
            excel2_cell = float(excel2.iloc[row, col])
            diff_value = excel1_cell - excel2_cell
            cell = sheet.cell(row+2, col+1)
            cell.value = diff_value
            if abs(diff_value) > threshold:
                cell.font = openpyxl.styles.Font(color="FF0000") # 红色
            else:
                cell.font = openpyxl.styles.Font(color="00FF00") # 绿色
            
    
    # 保存修改后的Excel文件
    wb.save(output_file)
    # 关闭Excel文件
    wb.close()

def write_excel(file_path, data):
    """
    将差值写入新的Excel文件中
    """
    writer = pd.ExcelWriter(file_path, engine='openpyxl')
    data.to_excel(writer, index=False)
    writer.close()

def adjust_column_width(file_path):
    """
    调整Excel文件中的列宽
    """
    wb = load_workbook(file_path)
    ws = wb.active
    
    for column in ws.columns:
        max_length = 0
        column_letter = column[0].column_letter
        for cell in column:
            try:
                if len(str(cell.value)) > max_length:
                    max_length = len(cell.value)
            except:
                pass
        adjusted_width = (max_length + 2) * 1.2
        ws.column_dimensions[column_letter].width = adjusted_width
    
    wb.save(file_path)
    wb.close()

